<?php
class DormCodeGen
{
    protected $dbh = null;

    public function connect()
    {
        $dbms='mysql';     //数据库类型
        $host='localhost'; //数据库主机名
        $dbName='elec_db';    //使用的数据库
        $user='elec';      //数据库连接用户名
        $pass='LXZ19961214';          //对应的密码
        $dsn="$dbms:host=$host;dbname=$dbName";

        try {
            $this->dbh = new PDO($dsn, $user, $pass); //初始化一个PDO对象
            echo "连接成功\n";
        } catch (PDOException $e) {
            die ("Error!: " . $e->getMessage());
        }
    }

    public function exec($sql)
    {
        if($this->dbh)
            return $this->dbh->exec($sql);
    }

    public function create()
    {
        $count = $this->exec("DROP TABLE IF EXISTS `dorm_code`;".
                    "CREATE TABLE `dorm_code` (".
                    "`dorm` char(8) NOT NULL,".
                    "`name` varchar(16) DEFAULT NULL,".
                    "PRIMARY KEY (`dorm`)".
                    ") ENGINE=InnoDB DEFAULT CHARSET=utf8;");
        print("Influenced $count rows.\n");
    }


    public function generate()
    {
        $t = array('A', 'B', 'C', 'D');
        $c = array(
                array(31, 12, 19, 13), // 学1 450
                array(33, 13, 20, 13), // 学2 474
                array(31, 12, 19, 13), // 学3 450
                array(33, 13, 20, 13), // 学4 474
                array(31, 12, 19, 13), // 学5 450
                array(36, 17, 36, 15), // 学6 624
                array(32, 10, 32,  0), // 学7 444
                array(32, 10, 32,  0), // 学8 444
                array(32, 10, 32,  0), // 学9 444
                array(36,  0,  0,  0), // 学10 216
                array(39,  0,  0,  0), // 学11 234
                array(35,  0,  0,  0)  // 学12 350
            );
        
        for($i = 0; $i < 24; $i++)      // 楼栋
        {
            for($j = 0; $j < 6; $j++)   // 楼层
            {
                for($k = 0; $k < $c[intval($i/4)][$i%4]; $k++) // 房间号
                {
                    $key = sprintf("%02d%02d%02d", $i + 1, $j + 1, $k + 1);
                    $value = sprintf("%d%s%d%02d", intval($i/4+1), $t[$i % 4], $j + 1, $k + 1);
                    $building[$key] = $value;
                }
            }
        }
        
        for($i = 24; $i < 33; $i++)      // 楼栋
        {
            for($j = 0; $j < 6; $j++)   // 楼层
            {
                for($k = 0; $k < $c[intval(($i-24)/3+6)][($i-24)%3]; $k++) // 房间号
                {
                    $key = sprintf("%02d%02d%02d", $i + 1, $j + 1, $k + 1);
                    $value = sprintf("%d%s%d%02d", intval(($i-24)/3+7), $t[($i-24) % 3], $j + 1, $k + 1);
                    $building[$key] = $value;
                }
            }
        }
        
        $f = array(6, 6, 10);
        for($i = 33; $i < 36; $i++)
        {
            for($j = 0; $j < $f[$i - 33]; $j++)
            {
                for($k = 0; $k < $c[$i-24][0]; $k++)
                {
                    $key = sprintf("%02d%02d%02d", $i + 1, $j + 1, $k + 1);
                    $value = sprintf("%d-%d%02d", $i - 23, $j + 1, $k + 1);
                    $building[$key] = $value;
                }
            }
        }
        //var_dump($building);

        $start = "INSERT INTO dorm_code(dorm, name) VALUES ";
        $end = ';';
        $i = 0;
        $sql = $start;
        $sum = 0;
        foreach($building as $key => $value)
        {
            if($i != 0) $sql .= ', ';
            $sql .= sprintf("('%s', '%s')", $key, $value);
            $i++;

            if($i >= 100)
            {
                $sql .= $end;
                //echo $sql."\n\n\n";
                $count = $this->exec($sql);
                $sum += $count;
                print("Influenced $count rows.\n");
                $sql = $start;
                $i = 0;
            }

        }

        $sql .= $end;
        $count = $this->exec($sql);
        $sum += $count;
        print("Influenced $count rows.\n");
        
        // 删除不存在的宿舍， 共164间
        $dorm_t =   "'010130', '010131', '050132', '050133', '090130', ".
                    "'090131', '130132', '130133', '170130', '170131', ".
                    "'210113', '210114', '210115', '210116', '210117', ". 
                    "'210118', '210119', '210120', '210121', '210122', ".
                    "'210123', '210124', '210125', '210126', '210127', ".
                    "'210128', '210129', '210130', '210131', '210132', ".
                    "'210133', '210134', '210135', '210136', '220116', ".
                    "'220117', '230114', '230115', '230116', '230117', ".
                    "'230118', '230119', '230120', '230121', '230122', ".
                    "'230123', '230124', '230125', '230126', '230127', ".
                    "'230128', '230129', '230130', '230131', '230132', ".
                    "'230133', '230134', '230135', '230136', '250113', ".
                    "'250114', '250115', '250116', '250117', '250118', ".
                    "'250119', '250120', '250121', '250122', '250123', ".
                    "'250124', '250125', '250126', '250127', '250128', ".
                    "'250129', '250130', '250131', '250132', '260101', ".
                    "'260102', '260103', '260104', '260105', '260106', ".
                    "'260107', '260108', '260109', '260110', '270113', ".
                    "'270114', '270115', '270116', '270117', '270118', ".
                    "'270119', '270120', '270121', '270122', '270123', ".
                    "'270124', '270125', '270126', '270127', '270128', ".
                    "'270129', '270130', '270131', '270132', '280113', ".
                    "'280114', '280115', '280116', '280117', '280118', ".
                    "'280119', '280120', '280121', '280122', '280123', ".
                    "'280124', '280125', '280126', '280127', '280128', ".
                    "'280129', '280130', '280131', '280132', '290101', ".
                    "'290102', '290103', '290104', '290105', '290106', ".
                    "'290107', '290108', '290109', '290110', '300126', ".
                    "'300127', '300128', '300129', '300130', '300131', ".
                    "'300132', '310129', '310130', '310131', '310132', ".
                    "'330129', '330130', '330131', '330132', '340134', ".
                    "'340135', '340136', '350137', '350138', '350139', ".
                    "'360120', '360121', '360134', '360135'"; 
        $sql = "DELETE FROM dorm_code WHERE dorm IN ($dorm_t);";
        
        $count = $this->exec($sql);
        $sum -= $count;
        print("Influenced $count rows.\n");
        print("Sum: $sum\n\n\n");
        
    }
}
$obj = new DormCodeGen();
$obj->connect();
$obj->create();
$obj->generate();
?>
